
package pe.com.losportales.sigespub.dao.impl;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pe.com.losportales.sigespub.dao.Coneccion;
import pe.com.losportales.sigespub.dao.PlanMarketingDao;
import pe.com.losportales.sigespub.modelo.Cliente;
import pe.com.losportales.sigespub.modelo.PlanMarketing;
import pe.com.losportales.sigespub.modelo.Proyecto;
import pe.com.losportales.sigespub.modelo.Responsable;
import pe.com.losportales.sigespub.modelo.TipoMedioCampanha;
import pe.com.losportales.sigespub.modelo.Usuario;

public class PlanMarketingDaoImpl implements PlanMarketingDao {

	@Override
	public List<PlanMarketing> obtenerPlanesMarketing(int codigoPlanMarketing)
			throws Exception {
		// TODO Auto-generated method stub
		List<PlanMarketing> listaPlanes = new ArrayList<PlanMarketing>();
		try{
			PlanMarketing planMarketing = null;
			CallableStatement cstmt = Coneccion.getConnection().prepareCall("{call UP_LISTAR_PLAN_MARKETING(?)}");
			cstmt.setInt(1, codigoPlanMarketing);
			cstmt.execute();
			ResultSet rs = cstmt.getResultSet();
			while (rs.next()) {
				planMarketing 			= new PlanMarketing();
				Cliente cliente 		= new Cliente();
				Usuario usuario 		= new Usuario();
				Responsable responsable = new Responsable();
				planMarketing.setCodigo(rs.getInt("COD_PLAN"));
				cliente.setCodigo(rs.getInt("COD_CLIENTE"));
				cliente.setRazonSocial(rs.getString("RAZ_SOC_CLIENTE"));
				usuario.setCodigo(rs.getInt("COD_USUARIO"));
				usuario.setNombre(rs.getString("USUARIO"));
				planMarketing.setCliente(cliente);
				planMarketing.setUsuario(usuario);
				responsable.setCodigo(rs.getInt("COD_RESPONSABLE"));
				responsable.setNombre(rs.getString("RESPONSABLE"));
				planMarketing.setResponsable(responsable);
				if(rs.getDate("FEC_APR_PLAN_MARKETING")!=null)
					planMarketing.setFechaAprobacion(rs.getDate("FEC_APR_PLAN_MARKETING"));
					
				planMarketing.setMoneda(rs.getString("MON_PLAN_MARKETING"));
				planMarketing.setCosto(rs.getFloat("COS_PLAN_MARKETING"));
				planMarketing.setEstado(rs.getBoolean("EST_PLAN_MARKETING"));
				
				listaPlanes.add(planMarketing);
			}
			rs.close();
			cstmt.close();
		}catch(SQLException s){
			throw new Exception(s);
		}finally{
			if( Coneccion.getConnection()!=null)
				Coneccion.getConnection().close();
		}
		
		
	
		
		return listaPlanes;
	}

	@Override
	public boolean aprobarPlanMarketing(int codigo) throws Exception {
		boolean resultado = true;
		try{
			CallableStatement cstmt = Coneccion.getConnection().prepareCall("{call UP_APROBAR_PLAN_MARKETING(?)}");
			cstmt.setInt(1, codigo);
			cstmt.execute();
			cstmt.close();
		}catch(SQLException s){
			resultado = false;
			throw new Exception(s);
		}finally{
			if( Coneccion.getConnection()!=null)
				Coneccion.getConnection().close();
		}
		
		
		
		
		
		return resultado;
	}

	@Override
	public int guardarPlanificacion(PlanMarketing planMarketing)
			throws Exception {
			
		int codigo = 0;
			try{
				CallableStatement cstmt = Coneccion.getConnection().prepareCall("{call UP_REGISTRAR_PLAN_MARKETING(?,?,?,?,?,?,?,?)}");
				cstmt.setInt(1, planMarketing.getVersion());
				cstmt.setInt(2, planMarketing.getCliente().getCodigo());
				cstmt.setInt(3, 1);
				cstmt.setInt(4, 1);
				cstmt.setDate(5, planMarketing.getFechaInicio());
				cstmt.setDate(6, planMarketing.getFechaAprobacion());
				cstmt.setString(7, planMarketing.getMoneda());
				cstmt.setFloat(8, planMarketing.getCosto());
				
				cstmt.execute();
				
				ResultSet rs = cstmt.getResultSet();
				while (rs.next()) {
					codigo = rs.getInt("codigo");
				}
				rs.close();
				cstmt.close();
			}catch(SQLException s){
				codigo = 0;
				throw new Exception(s);
			}finally{
				if( Coneccion.getConnection()!=null)
					Coneccion.getConnection().close();
			}

		
		
		
		return codigo;
	}


	@Override
	public void registrarProyectos(Proyecto p) throws Exception {
		
		try{
			CallableStatement cstmt = Coneccion.getConnection().prepareCall("{call UP_REGISTRAR_PLAN_MARKETING_PROYECTOS(?,?,?,?)}");
			cstmt.setInt(1, p.getPlan());
			cstmt.setInt(2, p.getCodigo());
			cstmt.setString(3, p.getDescripcion());
			cstmt.setInt(4, p.getParticipacion());
			cstmt.execute();
			cstmt.close();
		}catch(SQLException s){
			
			throw new Exception(s);
		}finally{
			if( Coneccion.getConnection()!=null)
				Coneccion.getConnection().close();
		}
		
	}

	@Override
	public void registrarTiposMedios(TipoMedioCampanha t)  throws Exception{
		
		
		try{
			CallableStatement cstmt = Coneccion.getConnection().prepareCall("{call UP_REGISTRAR_PLAN_MARKETING_TIPOS_MEDIO(?,?,?,?,?,?,?,?)}");
			cstmt.setInt(1, t.getPlan());
			cstmt.setInt(2, t.getTipoMedio());
			cstmt.setString(3, t.getDescripcion());
			cstmt.setString(4, t.getUnidad());
			cstmt.setInt(5, t.getCantidad());
			cstmt.setString(6, t.getMoneda());
			cstmt.setFloat(7, t.getCosto());
			cstmt.setFloat(8, t.getPrecio());
			cstmt.execute();
			cstmt.close();
		}catch(SQLException s){
			
			throw new Exception(s);
		}finally{
			if( Coneccion.getConnection()!=null)
				Coneccion.getConnection().close();
		}
		
	}

}
